ALTER PROCEDURE [dbo].[sprocCXCRMAutoLeadAssignment]
/* '===============================================================  
 '   NAME				:	[sprocCXCRMAutoLeadAssignment]
 '   DATE CREATED		:	30 Nov 2012
 '   CREATED BY			:	Phyo Kyaw Thant  
 '   CREATED FOR		:	CompareXpress - http://www.gluaygluay.com/
 '   FUNCTION			:	Auto new lead assignment of two hours old quote
  '===============================================================*/ 
AS

Declare @CallType smallint
Declare @AgentCount smallint
Declare @CarID bigint
Declare @AgentID bigint
Declare @CustomerID bigint
Declare @PendingStatus bit
Declare @OrderByOption smallint
Declare @MinPremium float
Declare @QuoteDateStart smallint
Declare @QuoteDateEnd smallint
Declare @CommenceDateStart smallint
Declare @CommenceDateEnd smallint

Declare @StartDate datetime
DEclare @EndDate datetime
Declare @StartCommDate date
Declare @EndCommDate date

Declare @strStartDate varchar(8)
DEclare @strEndDate varchar(8)

Select @MinPremium = MinPremium, 
				@QuoteDateStart = QuoteStart, 
				@QuoteDateEnd = QuoteEnd, 
				@CommenceDateStart = CommDateStart, 
				@CommenceDateEnd = CommDateEnd,
				@OrderByOption = OrderByOption
		From tblCXCRMLeadSetting Where [Status] = 1 And SettingID = 1

Select @EndCommDate = GETDATE() + @CommenceDateEnd
Select @StartCommDate = GETDATE() + @CommenceDateStart
Select @StartDate = GETDATE() - @QuoteDateStart
Select @EndDate = GETDATE() -- @QuoteDateEnd

Select @strStartDate = CONVERT(varchar(8), @StartDate, 112)
Select @strEndDate = CONVERT(varchar(8), @EndDate, 112)

Declare @NewCustomerCount int
Declare @Counter int
Declare @MaxCounter int

--Default Values
SET @PendingStatus = 0
SET @CallType = 100

--Agent Count
SET @AgentCount = 5

--Counter to control and limit lead NOT to be extra
SET @MaxCounter = 1
SET @Counter = 1

select @NewCustomerCount = Count(T.RefCEUserId)
FROM (
Select distinct c.RefCEUserId from tblCarDetailTH c 
	INNER JOIN tblCarPolicy cp ON c.CarID = cp.CECarID And c.RefCEUserID = cp.RefCEUserID
	INNER JOIN tblCEUsrProfile up ON c.RefCEUserID = up.RefCEUserID
	Where c.RefCEUserId NOT IN
	(Select l.CustomerID from tblCXCRMAgentLeads l Where (Convert(varchar(8), l.CreatedDate, 112) Between Convert(varchar(8), GETDATE() - 60, 112) And Convert(varchar(8), GETDATE(), 112)))
	AND (CONVERT(varchar(8), c.CreatedDate, 112) Between @strStartDate And @strEndDate) And c.RefCEUserId > 0
	AND (cp.CoverageCommenceDate Between @StartCommDate And @EndCommDate)
	AND ((c.Info7 NOT Like '%[A-Z,a-z]%' AND c.Info8 NOT Like '%[A-Z,a-z]%') OR (up.NInfo7 = 1))
) AS T

SET @MaxCounter = (@NewCustomerCount / @AgentCount) * @AgentCount

Select @NewCustomerCount NewCustomerCount, @MaxCounter MaxCounter

Declare curNewCustomer cursor for
Select distinct c.RefCEUserId from tblCarDetailTH c 
	INNER JOIN tblCarPolicy cp ON c.CarID = cp.CECarID And c.RefCEUserID = cp.RefCEUserID
	INNER JOIN tblCEUsrProfile up ON c.RefCEUserID = up.RefCEUserID
	Where c.RefCEUserId NOT IN
	(Select l.CustomerID from tblCXCRMAgentLeads l Where (Convert(varchar(8), l.CreatedDate, 112) Between Convert(varchar(8), GETDATE() - 60, 112) And Convert(varchar(8), GETDATE(), 112)))
	AND (CONVERT(varchar(8), c.CreatedDate, 112) Between @strStartDate And @strEndDate) And c.RefCEUserId > 0
	-- *****START 2 HOURS IS HERE*****
	AND DATEDIFF(hour, c.CreatedDate, GetDate()) >= 2 
	-- *****END 2 HOURS IS HERE*****
	AND (cp.CoverageCommenceDate Between @StartCommDate And @EndCommDate)
	AND ((c.Info7 NOT Like '%[A-Z,a-z]%' AND c.Info8 NOT Like '%[A-Z,a-z]%') OR (up.NInfo7 = 1))
	
	--547	broker1@gluaygluay.com
	--18808	broker3@gluaygluay.com
	--8543	broker5@gluaygluay.com
	--18813	broker6@gluaygluay.com
	--8546	broker7@gluaygluay.com
	--18818	broker8@comparexpress.co.th
	
	
	--Random Pick up
	SELECT TOP 1 @AgentID = CEID from tblCEUser Where CEID IN (547, 18808, 8543, 18813, 8546, 18818) Order By NewID()
	
	Select @AgentID RandomAgentID
	
	OPEN curNewCustomer 
	FETCH NEXT FROM curNewCustomer INTO @CustomerID
	WHILE (@@fetch_status <> -1 AND (@MaxCounter >= @Counter))
	BEGIN	

		Select top 1 @CarID = c.CarId from tblCarDetailTH c
		INNER JOIN tblCarPolicy cp ON c.CarID = cp.CECarID And c.RefCEUserID = cp.RefCEUserID
		Where c.RefCEUserId = @CustomerID And (CONVERT(varchar(8), c.CreatedDate, 112) Between @strStartDate And @strEndDate) 
		Order By CarId DESC
		
		IF(@CustomerID IS NOT NULL AND @CarID IS NOT NULL)
		BEGIN
			INSERT INTO tblCXCRMAgentLeads(CarID, AgentID, CustomerID, CallType, Status, IsFromService)
			Select CarID, @AgentID, RefCEUserID, @CallType, 1, 2 from tblCarDetailTH Where RefCEUserId = @CustomerID And CarId = @CarID
			--Select @Counter [Counter], CarID, @AgentID, RefCEUserID, @CallType, 1, 2 from tblCarDetailTH Where RefCEUserId = @CustomerID And CarId = @CarID
			
			SET @Counter = @Counter + 1
			
			--SELECT @AgentLeadID = SCOPE_IDENTITY()
			Select @Counter [Counter]
			
			IF @AgentID = 547 BEGIN SET @AgentID = 18808 END
			ELSE IF @AgentID = 18808 BEGIN SET @AgentID = 8543 END
			ELSE IF @AgentID = 8543 BEGIN SET @AgentID = 18813 END
			ELSE IF @AgentID = 18813 BEGIN SET @AgentID = 8546 END
			ELSE IF @AgentID = 8546 BEGIN SET @AgentID = 18818 END
			ELSE IF @AgentID = 18818 BEGIN SET @AgentID = 547 END
		END
	FETCH NEXT FROM curNewCustomer INTO @CustomerID
	
	END
	CLOSE curNewCustomer
	DEALLOCATE curNewCustomer